package MySql;

import java.sql.*;
import java.util.ArrayList;

public class introducesManager {
    public static String displayAllRows() throws SQLException {
        String sql="SELECT * FROM introduce";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                Statement st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            rs=st.executeQuery(sql); //返回结果集
            rs.last();//指针到最后一条记录
            int nRows=rs.getRow();//返回记录数
            if (nRows==0) {
                System.out.println("没有找到满足查询条件的记录！\n");
                return "没有找到满足查询条件的记录！\n";
            }else {
                rs.beforeFirst(); //指针到第一条记录之前
                StringBuilder buffer=new StringBuilder(); //动态字符串
                while (rs.next()) { //遍历记录集
                    buffer.append(rs.getString("id")).append(",");
                    buffer.append(rs.getString("kind")).append(",");
                    buffer.append(rs.getString("title")).append(",");
                    buffer.append(rs.getString("brief")).append(",");
                    buffer.append(rs.getString("img")).append("。");
                }//end while
//                System.out.println(buffer.toString());
                return buffer.toString();
            }//end if
        }catch (SQLException ex) {
        }finally {
            if (rs!=null) rs.close();
        }//end try
        return "";
    }//end displayAllRows

    public static ArrayList<Introduces> displayAllIntroduce() throws SQLException {
        String sql="SELECT * FROM introduce";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                Statement st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            rs=st.executeQuery(sql); //返回结果集
            rs.last();//指针到最后一条记录
            int nRows=rs.getRow();//返回记录数

            System.out.println(nRows);

            if (nRows==0) {
                System.out.println("没有找到满足查询条件的记录！\n");
                return null;
            }else {
                ArrayList<Introduces> introduces=new ArrayList<Introduces>();
                rs.beforeFirst(); //指针到第一条记录之前
                while (rs.next()) { //遍历记录集
                    String id=rs.getString("id");
                    String kind=rs.getString("kind");
                    String title = rs.getString("title");
                    String content = rs.getString("content");
                    String img = rs.getString("img");
                    Introduces introduce=new Introduces(id,kind,title,content,img);
                    introduces.add(introduce);
                }//end while
//                System.out.println(buffer.toString());
                return introduces;
            }//end if
        }catch (SQLException ex) {
        }finally {
            if (rs!=null) rs.close();
        }//end try
        return null;
    }//end displayAllRows

    //根据id查找记录
    public static Introduces getRowById(String id) throws SQLException {
        String sql="SELECT * FROM introduce WHERE id=?";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            st.setString(1, id); //设置参数
            rs=st.executeQuery(); //返回结果集
            if (rs.next()) { //找到
                Introduces bean=new Introduces(); //定义用户数据实体
                bean.setId(rs.getString("id"));
                bean.setKind(rs.getString("kind"));
                bean.setTitle(rs.getString("title"));
                bean.setContent(rs.getString("content"));
                bean.setImg(rs.getString("img"));
                return bean; //返回用户数据实体
            }else { //没找到
                return null;
            }
        } catch (SQLException ex) {
            return null;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }//end getRowById

    public static boolean registerIntroduce(Introduces bean) throws SQLException{
        System.out.println(bean.getId());
        System.out.println(testId(bean));
        if (testId(bean)) return false;//如果用户存在，则注册失败
        String sql="INSERT INTO introduce(id,kind,title,content,img) VALUES (?,?,?,?,?)";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            //设置参数
//            System.out.println(bean.getKind());
//            System.out.println(bean.getTitle());
//            System.out.println(bean.getBrief());
//            System.out.println(bean.getPrice());
//            System.out.println(bean.getImg());

            st.setString(1,bean.getId());
            st.setString(2,bean.getKind());
            st.setString(3,bean.getTitle());
            st.setString(4,bean.getContent());
            st.setString(5,bean.getImg());


            int affected=st.executeUpdate();
            System.out.println(affected);
            return affected==1; //注册成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
        }//end try
    }//end registerUser

    public static boolean testId(Introduces bean) throws SQLException{
        String sql="SELECT * FROM introduce WHERE id=? ";
        ResultSet rs=null;
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            //设置参数
            st.setString(1,bean.getId());

            rs=st.executeQuery(); //返回结果集
            return rs.next(); //登录成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }

    //    public static boolean userLogin(User bean) throws SQLException {
//        String sql="SELECT * FROM usermember WHERE username=? AND passwd=?";
//        ResultSet rs=null;
//        try (
//                Connection conn=DBUtils.getConnection();
//                PreparedStatement st=conn.prepareStatement(sql,
//                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//        ){
//            //设置参数
//            st.setString(1,bean.getUsername());
//            st.setString(2, bean.getPasswd());
//            rs=st.executeQuery(); //返回结果集
//            return rs.next(); //登录成功或失败
//        } catch (SQLException ex) {
//            return false;
//        }finally {
//            if (rs!=null) rs.close();
//        }//end try
//    }//end userLogin
    //修改用户
    public static boolean updateIntroduce(Introduces bean) {
        String sql="UPDATE introduce SET id=? , kind= ? ,"
                + " title=? , content= ? , img= ? WHERE id=?";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            //设置参数
            st.setString(1, bean.getId());
            st.setString(2, bean.getKind());
            st.setString(3, bean.getTitle());
            st.setString(4,bean.getContent());
            st.setString(5, bean.getImg());
            st.setString(6,bean.getId());
            int affected=st.executeUpdate();
            return affected==1; //修改成功或失败
        } catch (SQLException ex) {
            return false;
        }//end try
    }//end updateUser
    //删除用户
    public static boolean deleteIntroduce(String Id) {
        String sql="DELETE FROM introduce WHERE id=?";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            st.setString(1,Id);
            int affected=st.executeUpdate();
            return affected==1; //删除成功或失败
        } catch (SQLException ex) {
            return false;
        }//end try
    }//end updateUser
}
